﻿----------------------------------------------------------------------------------------------------
--
--  © 2012 Stanislav S. Yarmonov.
--
--  TODO:LICENSE
--     
----------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------
--
--	Database clearing routine.
--
----------------------------------------------------------------------------------------------------
SET NOEXEC OFF
GO

SET NOCOUNT ON
GO

-- Ensure, that we are not clearing system databases -----------------------------------------------
--TODO:Better to limit for current database only
DECLARE @DB [sysname] SET @DB = QUOTENAME(DB_NAME())
IF @DB IN ('[master]', '[model]', '[msdb]', '[tempdb]')
OR EXISTS(
	SELECT
		NULL
	FROM
		[sys].[databases]
	WHERE
		([name] = @DB)
		AND ([owner_sid] = SUSER_SID('sa'))
)
BEGIN
	RAISERROR('Aborted to prevent clearing system database [%s].', 18, 1, @DB)
	SET NOEXEC ON
END ELSE BEGIN
	SET NOEXEC OFF
END
GO

-- Swithc database to single user mode, so nobody blocking the clearing routine --------------------
DECLARE @SQL [nvarchar](max) SELECT @SQL =
	N'ALTER DATABASE '
	+ QUOTENAME(DB_NAME())
	+ N' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
EXECUTE [dbo].[sp_executesql] @SQL
GO

-- Setup the clearing priorities -------------------------------------------------------------------
DECLARE @Priority TABLE (
	[Type] [char](2) NOT NULL,
	[Priority] [int] NOT NULL
)

INSERT INTO @Priority
SELECT 'F', 100
UNION ALL SELECT 'C', 100
UNION ALL SELECT 'D', 100
UNION ALL SELECT 'PK', 200
UNION ALL SELECT 'FN', 300
UNION ALL SELECT 'TF', 300
UNION ALL SELECT 'IF', 300
UNION ALL SELECT 'P', 300
UNION ALL SELECT 'V', 300
UNION ALL SELECT 'TR', 300
UNION ALL SELECT 'U', 400

-- Clear all objects -------------------------------------------------------------------------------
DECLARE @CR [varchar](2) SET @CR = ';' + CHAR(13)
DECLARE @SQL [nvarchar](max) SELECT @SQL = N''
DECLARE @Statement [nvarchar](max) SET @Statement = N''
DECLARE @Count [int] SET @Count = 1

WHILE @Count > 0 BEGIN
	SET @SQL = ''

	SELECT
		@Statement = CASE
			WHEN [ObjectType] IN ('IDX') AND [ParentType] IN ('U')
				THEN 'DROP INDEX {ParentSchema}.{Parent}.{Object}'
			WHEN [ObjectType] IN ('COL') AND [ParentType] IN ('U')
				THEN 'ALTER TABLE {ParentSchema}.{Parent} DROP COLUMN {Object}'
			WHEN [ObjectType] IN ('PK', 'F', 'C', 'D') AND [ParentType] IN ('U')
				THEN 'ALTER TABLE {ParentSchema}.{Parent} DROP CONSTRAINT {Object}'
			WHEN [ObjectType] IN ('PK') AND [ParentType] IN ('TF')
				THEN ''
			WHEN [ObjectType] IN ('FN', 'TF', 'IF')
				THEN 'DROP FUNCTION {ObjectSchema}.{Object}'
			WHEN [ObjectType] IN ('P')
				THEN 'DROP PROCEDURE {ObjectSchema}.{Object}'
			WHEN [ObjectType] IN ('V')
				THEN 'DROP VIEW {ObjectSchema}.{Object}'
			WHEN [ObjectType] IN ('TR')
				THEN 'DROP TRIGGER {ObjectSchema}.{Object}'
			WHEN [ObjectType] IN ('U')
				THEN 'DROP TABLE {ObjectSchema}.{Object}'
			ELSE 'RAISERROR(''Unknown type [{Type}] for {ObjectSchema}.{Object}'', 16, 1)'
		END,
		@Statement = REPLACE(@Statement, '{Type}', [ObjectType]),
		@Statement = REPLACE(@Statement, '{Object}', QUOTENAME([ObjectName])),
		@Statement = REPLACE(
			@Statement,
			'{ObjectSchema}',
			ISNULL(
				QUOTENAME(SCHEMA_NAME([ObjectUID])),
				'[dbo]'
			)
		),
		@Statement = CASE
			WHEN [ParentID] IS NULL THEN @Statement
			ELSE REPLACE(@Statement, '{Parent}', QUOTENAME([ParentName]))
		END,
		@Statement = CASE
			WHEN [ParentID] IS NULL THEN @Statement
			ELSE REPLACE(@Statement, '{ParentSchema}', QUOTENAME(SCHEMA_NAME([ParentUID])))
		END,
		@SQL = @SQL + @Statement + @CR
	FROM
		(
			SELECT DISTINCT
				[Object].[uid] AS [ObjectUID],
				[Object].[xtype] AS [ObjectType],
				[Object].[name] AS [ObjectName],
				[Parent].[id] AS [ParentID],
				[Parent].[uid] AS [ParentUID],
				[Parent].[xtype] AS [ParentType],
				[Parent].[name] AS [ParentName],
				[Priority].[Priority] AS [Priority]
			FROM
				[dbo].[sysobjects] AS [Object]
				LEFT OUTER JOIN [dbo].[sysdepends] AS [Depends] ON ([Depends].[depid] = [Object].[id])
				LEFT OUTER JOIN [dbo].[sysobjects] AS [Parent] ON ([Parent].[ID] = [Object].[parent_obj])
				LEFT OUTER JOIN @Priority AS [Priority] ON ([Priority].[Type] = [Object].[xtype])
			WHERE
				OBJECTPROPERTY([Object].[id], N'IsMSShipped') = 0
				AND (
					-- Objects without dependencies
					([Depends].[id] IS NULL)
					-- Objects, those are depended recursively (excluding tables)
					OR (
						([Object].[id] = [Depends].[id])
						AND ([Object].[xtype] <> 'U')
					)
				)

			-- Индексы
			UNION ALL SELECT DISTINCT TOP 100 PERCENT
				[si].[indid] AS [ObjectUID],
				'IDX' AS [ObjectType],
				[si].[name] AS [ObjectName],
				[so].[id] AS [ParentID],
				[so].[uid] AS [ParentUID],
				[so].[xtype] AS [ParentType],
				[so].[name] AS [ParentName],
				0 AS [Priority]
			FROM
				[dbo].[sysindexes] AS [si]
				INNER JOIN [dbo].[sysobjects] AS [so] ON ([si].[id] = [so].[id])
				LEFT OUTER JOIN [dbo].[sysobjects] AS [soi] ON (
					[soi].[parent_obj] = [so].[id]
					AND [soi].[name] = [si].[name]
				)
			WHERE
				([so].[xtype] = 'U')
				AND ([si].[name] IS NOT NULL)
				AND ([soi].[xtype] IS NULL)
				AND (OBJECTPROPERTY([so].[id], N'IsMSShipped') = 0)
				AND (INDEXPROPERTY([so].[id], [si].[name], 'IsAutoStatistics') = 0)

			-- Cyclic references for columns + UDFs
			UNION ALL SELECT DISTINCT TOP 100 PERCENT
				[sc].[colid] AS [ObjectUID],
				'COL' AS [ObjectType],
				[sc].[name] AS [ObjectName],
				[so].[id] AS [ParentID],
				[so].[uid] AS [ParentUID],
				[so].[xtype] AS [ParentType],
				[so].[name] AS [ParentName],
				0 AS [Priority]
			FROM
				[dbo].[sysdepends] AS [sd]
				INNER JOIN [dbo].[sysobjects] AS [so] ON ([so].[id] = [sd].[id])
				INNER JOIN [dbo].[syscolumns] AS [sc] ON (
					([sc].[id] = [sd].[id])
					AND ([sc].[colid] = [sd].[number])
				)
			WHERE
				([sd].[id] = [sd].[depid])
				AND ([sd].[number] <> 0)
				AND ([so].[xtype] IN ('U'))
		) AS [T]
	ORDER BY
		[Priority] ASC

	SET @Count = @@ROWCOUNT

	EXECUTE [dbo].[sp_executesql] @SQL
	
	IF @@ERROR <> 0 SET @Count = -1
END
GO

-- Clear user types --------------------------------------------------------------------------------
DECLARE @CR [varchar](2) SET @CR = ';' + CHAR(13)
DECLARE @SQL [nvarchar](max) SELECT @SQL = ''
SELECT
	@SQL = @SQL + N'DROP TYPE '
		+ QUOTENAME(SCHEMA_NAME([type].[schema_id])) + N'.' + QUOTENAME([type].[name])
	+ @CR
FROM
	[sys].[types] AS [type]
WHERE
	([is_user_defined] = 1)

EXECUTE [dbo].[sp_executesql] @SQL
GO

-- Clear schemas -----------------------------------------------------------------------------------
DECLARE @CR [varchar](2) SET @CR = ';' + CHAR(13)
DECLARE @SQL [nvarchar](max) SELECT @SQL = ''
SELECT
	@SQL = @SQL + 'DROP SCHEMA '
		+ QUOTENAME([name])
	+ @CR
FROM
	[sys].[schemas]
WHERE
	([principal_id] <> [schema_id])

EXECUTE [dbo].[sp_executesql] @SQL
GO

-- Switch database back to multi-user mode ---------------------------------------------------------
DECLARE @SQL [nvarchar](max) SELECT @SQL =
	N'ALTER DATABASE '
	+ QUOTENAME(DB_NAME())
	+ N' SET MULTI_USER WITH ROLLBACK IMMEDIATE'
EXECUTE [dbo].[sp_executesql] @SQL
GO

SET NOCOUNT OFF
GO

